﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreedomERP.Dao.Inv;
using FreedomERP.Dao.Inv.Impl;
using FreedomERP.Models;
using System.IO;
using NPOI.HSSF.UserModel;
using FreedomERP.Comm.Utils;
using NPOI.SS.UserModel;
using FreedomERP.Models.Inv;
using FreedomERP.Comm;
using FreedomERP.Dao.Bd.Impl;
using FreedomERP.Dao.Bd;
using FreedomERP.Services.Bd;
using FreedomERP.Services.Bd.Impl;
using NPOI.SS.Util;

namespace FreedomERP.Services.Inv.Impl
{
    /// <summary>
    /// 送货单业务层实现类
    /// 作者：姚少鹏
    /// </summary>
    public class InvSendOrderServiceImpl : IInvSendOrderService
    {
        IInvSendOrderDao sendOrderDao = new InvSendOrderDaoImpl();
        IBdCategoryDao categoryDao = new BdCategoryDaoImpl();
        IInvShortageOrderService shortageOrderService = new InvShortageOrderServiceImpl();

        /// <summary>
        /// 添加送货单
        /// </summary>
        /// <param name="sendOrder"></param>
        public void addSendOrder(InvSendOrder sendOrder)
        {
            sendOrderDao.addSendOrder(sendOrder);
        }

        /// <summary>
        /// 删除送货单
        /// </summary>
        /// <author>陈新杰</author>
        /// <param name="FId"></param>
        public void delSendOrder(string FId)
        {
            sendOrderDao.delSendOrder(FId);
        }

        /// <summary>
        /// 更新送货单
        /// </summary>
        /// <param name="sendOrder"></param>
        public void updateSendOrder(InvSendOrder sendOrder)
        {
            sendOrderDao.updateSendOrder(sendOrder);
        }

        /// <summary>
        /// 根据FId获取送货单
        /// </summary>
        /// <param name="FId"></param>
        /// <returns></returns>
        public InvSendOrder getSendOrderByFId(string FId)
        {
            return sendOrderDao.getSendOrderByFId(FId);
        }

        /// <summary>
        /// 获取送货单列表
        /// </summary>
        /// <returns></returns>
        public List<InvSendOrder> getSendOrderList()
        {
            return sendOrderDao.getSendOrderList();
        }

       /// <summary>
       /// 获取送货单列表
       /// </summary>
       /// <param name="FStartTime"></param>
       /// <param name="FEndTime"></param>
       /// <param name="FCustomerID"></param>
       /// <param name="FOrderNumber"></param>
       /// <param name="FStatusId"></param>
       /// <param name="total"></param>
       /// <returns></returns>
        public List<InvSendOrder> getSendOrderList(string FStartTime, string FEndTime, string FCustomerID, string FOrderNumber, string FStatusId, int start, int limit, out int total, string currAccountOrgunitFId)
        {
            return sendOrderDao.getSendOrderList(FStartTime, FEndTime, FCustomerID, FOrderNumber, FStatusId, start, limit, out total, currAccountOrgunitFId);
        }

        /// <summary>
        /// 导出送货单信息到excel
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void ExportSendOrderToExcel(String FId, String SortDirect,string serverPath, HttpResponseBase response)
        {
            InvSendOrder sendOrder = sendOrderDao.getSendOrderByFId(FId);
            BdCategory statusCate = sendOrder.BdCategory;
            if (statusCate.FNumber != ConstData.CATEGORY_SEND_ORDER_STATUS_APPROVE)
            {
                throw new Exception("只有已审核的送货单才可以打印。");
            }

            if (sendOrder != null)
            {
                string templateFilePath = @"" + serverPath + "Upload\\Template\\SendOrderExportTemplate.xls";
                FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);

                MemoryStream ms = new MemoryStream();

                HSSFWorkbook workbook = new HSSFWorkbook(file);
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

                HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;

                if (sendOrder.BdCustomer != null)
                {
                    HSSFCell customerCell = (HSSFCell)sheet.GetRow(3).CreateCell(1);
                    String customerDetail = sendOrder.BdCustomer.FName;
                    bool hadTwoMobileNum = false;
                    bool hadOneMobileNum = false;
                    String mobile = sendOrder.BdCustomer.FContactMobile;
                    String custumerPhone = sendOrder.BdCustomer.FContactPhone;
                    if (mobile != null || mobile != "" || custumerPhone != null || custumerPhone!="")
                    {
                        customerDetail += "  " + "电话：";
                    }
                    if (custumerPhone != null && custumerPhone != "")
                    {
                       
                        if (custumerPhone.Contains(",") || mobile.Contains("，"))
                        {
                            hadTwoMobileNum=true;
                            customerDetail += custumerPhone.Replace(",", " ").Replace("，", " ");
                        }
                        else
                        {
                            customerDetail += custumerPhone;
                            hadOneMobileNum = true;
                        }
                    }
                    if (!hadTwoMobileNum && mobile != null && mobile != "")
                    {
                        if (mobile.Contains(",") || mobile.Contains("，"))
                        {
                            if (!hadOneMobileNum)
                            {
                                mobile = mobile.Replace(",", " ").Replace("，", " ");
                                customerDetail += mobile;
                            }
                            else
                            {
                                if (mobile.Contains(","))
                                {
                                    mobile = mobile.Substring(0, mobile.IndexOf(","));
                                }
                                else
                                {
                                    mobile = mobile.Substring(0, mobile.IndexOf("，"));
                                }
                                customerDetail +=" "+ mobile;
                            }

                        }
                        else
                        {
                            customerDetail += " " + mobile;
                        }
                        
                    }
                    if (sendOrder.BdCustomer.FContactAddress != null && sendOrder.BdCustomer.FContactAddress!="")
                    {
                         customerDetail+="  "+"地址："+sendOrder.BdCustomer.FContactAddress;
                    }
                    customerCell.SetCellValue(customerDetail);
                }

                if (sendOrder.FBillDate != null)
                {
                    HSSFCell billDateCell = (HSSFCell)sheet.GetRow(3).CreateCell(7);
                    billDateCell.SetCellValue(DateUtils.FormatDate(sendOrder.FBillDate,"yyyy-MM-dd"));
                }

                if (sendOrder.InvSendOrderDetail != null) 
                {
                    int r = 5;
                    long qtyCount = 0L;
                    double amountCount = 0d;
                    IOrderedEnumerable<InvSendOrderDetail> orderedEnumerable = null;
                    if ("asc".Equals(SortDirect) || "".Equals(SortDirect) || "undefined".Equals(SortDirect))
                        orderedEnumerable=sendOrder.InvSendOrderDetail.OrderBy(ordDetail =>ordDetail.BdProduct.FNumber);
                    else
                        orderedEnumerable = sendOrder.InvSendOrderDetail.OrderByDescending(ordDetail => ordDetail.BdProduct.FNumber);
                    foreach (InvSendOrderDetail detail in orderedEnumerable)
                    {
                        BdProduct product = detail.BdProduct;

                        HSSFRow row = (HSSFRow)sheet.CreateRow(r);

                        if (product != null)
                        {
                            HSSFCell codeCell = (HSSFCell)row.CreateCell(0);
                            codeCell.SetCellValue(product.FNumber);
                            codeCell.CellStyle = style;

                            HSSFCell nameCell = (HSSFCell)row.CreateCell(1);
                            nameCell.SetCellValue(product.FName);
                            nameCell.CellStyle = style;

                            if (product.BdCategory != null)
                            {
                                HSSFCell modelCell = (HSSFCell)row.CreateCell(2);
                                modelCell.SetCellValue(product.BdCategory.FName);
                                modelCell.CellStyle = style;
                            }
                        }

                       

                        HSSFCell qtyCell = (HSSFCell)row.CreateCell(3);
                        qtyCell.SetCellValue(detail.FQty);
                        qtyCell.CellStyle = style;

                        HSSFCell numberCell = (HSSFCell)row.CreateCell(4);
                        numberCell.SetCellValue(detail.FNumber);
                        numberCell.CellStyle = style;

                        HSSFCell priceCell = (HSSFCell)row.CreateCell(5);
                        priceCell.SetCellValue((double)detail.FPrice);
                        priceCell.CellStyle = style;

                        HSSFCell amountCell = (HSSFCell)row.CreateCell(6);
                        amountCell.SetCellValue((double)detail.FAmount);
                        amountCell.CellStyle = style;

                        HSSFCell remarkCell = (HSSFCell)row.CreateCell(7);
                        remarkCell.SetCellValue(detail.FRemark);
                        remarkCell.CellStyle = style;

                        qtyCount += detail.FQty;
                        amountCount += (double)detail.FAmount;
                        r++;
                    }

                    HSSFRow rowCount = (HSSFRow)sheet.CreateRow(r);
                    for (int rt = 0; rt < 8; rt++)
                    {
                        HSSFCell countCell = (HSSFCell)rowCount.CreateCell(rt);
                        countCell.CellStyle = style;

                        if (rt == 0) {
                            countCell.SetCellValue("合计：");
                        }
                        else if (rt == 7) 
                        {
                            countCell.SetCellValue("共"+qtyCount+"件");
                        }
                        else if (rt == 1)
                        {
                            MoneyUtils money = new MoneyUtils(amountCount);
                            countCell.SetCellValue(money.Convert());
                        }
                        else if (rt == 3)
                        {
                            MoneyUtils money = new MoneyUtils(amountCount);
                            countCell.SetCellValue(money.Convert());
                            HSSFCellStyle amountStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                            amountStyle.BorderBottom = BorderStyle.THIN;
                            amountStyle.BorderLeft = BorderStyle.NONE;
                            amountStyle.BorderRight = BorderStyle.NONE;
                            amountStyle.BorderTop = BorderStyle.THIN;
                            amountStyle.Alignment = HorizontalAlignment.LEFT;
                            countCell.CellStyle = amountStyle;
                        }
                        else if (rt == 4)
                        {
                            countCell.SetCellValue(amountCount+"元");
                            HSSFCellStyle amountStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                            amountStyle.BorderBottom = BorderStyle.THIN;
                            amountStyle.BorderLeft = BorderStyle.NONE;
                            amountStyle.BorderRight = BorderStyle.NONE;
                            amountStyle.BorderTop = BorderStyle.THIN;
                            amountStyle.Alignment = HorizontalAlignment.RIGHT;
                            countCell.CellStyle = amountStyle;

                        }
                        CellRangeAddress cellRangeAddress1 = new CellRangeAddress(r, r, 1, 3);
                        CellRangeAddress cellRangeAddress2 = new CellRangeAddress(r, r, 4, 6);
                        sheet.AddMergedRegion(cellRangeAddress1);
                        sheet.AddMergedRegion(cellRangeAddress2);
                    }

                }

                workbook.Write(ms);
                response.AddHeader("Content-Disposition", string.Format("attachment; filename=SendOrder_" + sendOrder .FNumber+ ".xls"));
                response.BinaryWrite(ms.ToArray());
                workbook = null;
                ms.Close();
                ms.Dispose();
            }
        }

        /// <summary>
        /// 添加送货单
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void AddSendOrderNew(InvSendOrderView sendOrderView, string currAccountFId, string currAccountOrgunitFId, out string FId, out string FNumber)
        {
            
            InvSendOrder sendOrder = new InvSendOrder();
            sendOrder.FId = GuidUtils.getGuid();
            sendOrder.FNumber = ConstData.SEND_ORDER_NUMBER_HEAD + DateUtils.GetCurDateFormat("yyyyMMddHHmmss");
            sendOrder.FName = sendOrder.FNumber;
            sendOrder.FBillDate = DateUtils.ParseStringToDateTime(sendOrderView.FBillDate);
            sendOrder.FCreateTime = DateUtils.GetCurDate();
            sendOrder.FCreatorId = currAccountFId;
            sendOrder.FModifyTime = DateUtils.GetCurDate();
            sendOrder.FModifierId = currAccountFId;
            sendOrder.FRemark = sendOrderView.FRemark;

            sendOrder.FStatusId = sendOrderView.FStatusId;
            sendOrder.FCustomerId = sendOrderView.FCustomerId;
            IBdOrgunitService orgService= new BdOrgunitServiceImpl();
            //String orgunitId=orgService.findOrgunitId(ConstData.ORGUNIT_NAME_LC);
            sendOrder.FOrgunitId = currAccountOrgunitFId;

            if (sendOrderView.FDetails != null)
            {
                foreach (InvSendOrderDetailView detailView in sendOrderView.FDetails)
                {
                    InvSendOrderDetail detail = new InvSendOrderDetail();
                    detail.FId = GuidUtils.getGuid();
                    detail.FProductId = detailView.FProductId;
                    detail.FQty = detailView.FQty;
                    detail.FNumber = detailView.FNumber;
                    detail.FPrice = detailView.FPrice;
                    detail.FAmount = detailView.FAmount;
                    detail.FRemark = detailView.FRemark;

                    sendOrder.InvSendOrderDetail.Add(detail);
                }
            }

            sendOrderDao.addSendOrder(sendOrder);

            FId = sendOrder.FId;
            FNumber = sendOrder.FNumber;
        }

        /// <summary>
        /// 修改送货单
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void ModifySendOrderNew(InvSendOrderView sendOrderView, string currAccountFId)
        {
            InvSendOrder objOld = sendOrderDao.getSendOrderByFId(sendOrderView.FId);

            if (objOld.BdCategory != null && objOld.BdCategory.FNumber.Equals(ConstData.CATEGORY_SEND_ORDER_STATUS_APPROVE))
            {
                throw new Exception(ConstNoteMessage.SEND_ORDER_MODIFY_ERROR);
            }

            if (objOld != null)
            {
                objOld.FNumber = sendOrderView.FNumber;
                objOld.FName = objOld.FNumber;
                objOld.FBillDate = DateUtils.ParseStringToDateTime(sendOrderView.FBillDate);
                objOld.FModifyTime = DateUtils.GetCurDate();
                objOld.FModifierId = currAccountFId;
                objOld.FRemark = sendOrderView.FRemark;
                objOld.FStatusId = sendOrderView.FStatusId;
                objOld.FCustomerId = sendOrderView.FCustomerId;

                //判断是否删除明细
                List<InvSendOrderDetail> deleteList = new List<InvSendOrderDetail>();
                if (objOld.InvSendOrderDetail != null)
                {
                    foreach (InvSendOrderDetail detail in objOld.InvSendOrderDetail)
                    {
                        if (sendOrderView.FDetails != null)
                        {
                            bool flag = false;
                            foreach (InvSendOrderDetailView detailView in sendOrderView.FDetails)
                            {
                                if (detail.FId.Equals(detailView.FId))
                                {
                                    flag = true;
                                    break;
                                }
                            }
                            if (!flag)
                            {
                                deleteList.Add(detail);
                            }
                        }
                    }
                }

                //添加或修改明细
                if (sendOrderView.FDetails != null)
                {
                    foreach (InvSendOrderDetailView detailView in sendOrderView.FDetails)
                    {
                        if (objOld.InvSendOrderDetail != null && detailView.FId != null && !detailView.FId.Trim().Equals("") && !detailView.FId.Trim().Equals("-1"))
                        {
                            var query = (from d in objOld.InvSendOrderDetail where d.FId == detailView.FId select d).ToList<InvSendOrderDetail>();
                            if (query != null && query.Count > 0)
                            {
                                InvSendOrderDetail detailOld = query.First<InvSendOrderDetail>();
                                detailOld.FProductId = detailView.FProductId;
                                detailOld.FQty = detailView.FQty;
                                detailOld.FNumber = detailView.FNumber;
                                detailOld.FPrice = detailView.FPrice;
                                detailOld.FAmount = detailView.FAmount;
                                detailOld.FRemark = detailView.FRemark;
                            }
                            else
                            {
                                InvSendOrderDetail detail = new InvSendOrderDetail();
                                detail.FId = GuidUtils.getGuid();
                                detail.FProductId = detailView.FProductId;
                                detail.FQty = detailView.FQty;
                                detail.FNumber = detailView.FNumber;
                                detail.FPrice = detailView.FPrice;
                                detail.FAmount = detailView.FAmount;
                                detail.FRemark = detailView.FRemark;

                                objOld.InvSendOrderDetail.Add(detail);
                            }
                        }
                        else
                        {
                            InvSendOrderDetail detail = new InvSendOrderDetail();
                            detail.FId = GuidUtils.getGuid();
                            detail.FProductId = detailView.FProductId;
                            detail.FQty = detailView.FQty;
                            detail.FNumber = detailView.FNumber;
                            detail.FPrice = detailView.FPrice;
                            detail.FAmount = detailView.FAmount;
                            detail.FRemark = detailView.FRemark;

                            objOld.InvSendOrderDetail.Add(detail);
                        }
                    }
                }

                //删除明细
                foreach (InvSendOrderDetail objDelete in deleteList)
                {
                    objOld.InvSendOrderDetail.Remove(objDelete);
                }

                sendOrderDao.Save();
            }
        }

        /// <summary>
        /// 审核送货单
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void ApproveSendOrder(string FId, string currAccountFId)
        {
            sendOrderDao.approveSendOrder( FId,  currAccountFId);
        }

        /// <summary>
        ///  更改欠货单（包括状态）
        /// </summary>
        /// <param name="FId"></param>
        /// <param name="FCustomerId"></param>
        /// <param name="FRemark"></param>
        /// <param name="FStatusId"></param>
        /// <param name="FStatusNumber"></param>
        /// <param name="FUpdateAcoountId"></param>
        public void UpdateSendOrder(string FId, string FCustomerId, string FRemark, string FStatusId, string FStatusNumber, string FUpdateAcoountId)
        {
            sendOrderDao.updateSendOrder( FId,  FCustomerId,  FRemark,  FStatusId,  FStatusNumber,  FUpdateAcoountId);
        }

        /// <summary>
        /// 审核送货单(格力)
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void ApproveSendOrderGeLi(string FId, string currAccountFId) 
        {
            sendOrderDao.approveSendOrderGeLi(FId, currAccountFId);
        }

        /// <summary>
        /// 导出送货单信息到excel(格力)
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void ExportSendOrderToExcelGeLi(string FId, String SortDirect, string serverPath, HttpResponseBase response)
        {
            InvSendOrder sendOrder = sendOrderDao.getSendOrderByFId(FId);
            BdCategory statusCate = sendOrder.BdCategory;
            if (statusCate.FNumber != ConstData.CATEGORY_SEND_ORDER_STATUS_APPROVE)
            {
                throw new Exception("只有已审核的送货单才可以打印。");
            }

            if (sendOrder != null)
            {
                string templateFilePath = @"" + serverPath + "Upload\\Template\\SendOrderGeLiExportTemplate.xls";
                FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);

                MemoryStream ms = new MemoryStream();

                HSSFWorkbook workbook = new HSSFWorkbook(file);
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

                HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;

                HSSFCell customerCell = (HSSFCell)sheet.GetRow(3).CreateCell(0);
                customerCell.SetCellValue("客户名称：" + sendOrder.FCustomerName);

                HSSFCell customerPhoneCell = (HSSFCell)sheet.GetRow(3).CreateCell(1);
                customerPhoneCell.SetCellValue("电话：" + sendOrder.FCustomerPhone + " " + sendOrder.FCustomerMobile);

                if (sendOrder.FBillDate != null)
                {
                    HSSFCell billDateCell = (HSSFCell)sheet.GetRow(3).CreateCell(2);
                    billDateCell.SetCellValue("日期：" + DateUtils.FormatDate(sendOrder.FBillDate, "yyyy-MM-dd"));
                }

                HSSFCell orderNumberCell = (HSSFCell)sheet.GetRow(3).CreateCell(4);
                orderNumberCell.SetCellValue("NO：" + sendOrder.FNumber);

                HSSFCell customerAddressCell = (HSSFCell)sheet.GetRow(4).CreateCell(0);
                customerAddressCell.SetCellValue("客户地址：" + sendOrder.FCustomerAddress);

                if (sendOrder.InvSendOrderDetail != null)
                {
                    int r = 6;
                    double amountCount = 0d;
                    IOrderedEnumerable<InvSendOrderDetail> orderedEnumerable = null;
                    if ("asc".Equals(SortDirect) || "".Equals(SortDirect) || "undefined".Equals(SortDirect))
                        orderedEnumerable = sendOrder.InvSendOrderDetail.OrderBy(ordDetail => ordDetail.BdProduct.FNumber);
                    else
                        orderedEnumerable = sendOrder.InvSendOrderDetail.OrderByDescending(ordDetail => ordDetail.BdProduct.FNumber);
                    foreach (InvSendOrderDetail detail in orderedEnumerable)
                    {
                        BdProduct product = detail.BdProduct;

                        HSSFRow row = (HSSFRow)sheet.CreateRow(r);

                        if (product != null)
                        {
                            HSSFCell nameCell = (HSSFCell)row.CreateCell(0);
                            nameCell.SetCellValue(product.FName);
                            nameCell.CellStyle = style;

                            if (product.BdCategory != null)
                            {
                                HSSFCell modelCell = (HSSFCell)row.CreateCell(1);
                                modelCell.SetCellValue(product.BdCategory.FName);
                                modelCell.CellStyle = style;
                            }

                            if (product.BdCategory2 != null)
                            {
                                HSSFCell unitCell = (HSSFCell)row.CreateCell(2);
                                unitCell.SetCellValue(product.BdCategory2.FName);
                                unitCell.CellStyle = style;
                            }
                        }



                        HSSFCell qtyCell = (HSSFCell)row.CreateCell(3);
                        qtyCell.SetCellValue(detail.FQty);
                        qtyCell.CellStyle = style;

                        HSSFCell priceCell = (HSSFCell)row.CreateCell(4);
                        priceCell.SetCellValue((double)detail.FPrice);
                        priceCell.CellStyle = style;

                        HSSFCell amountCell = (HSSFCell)row.CreateCell(5);
                        amountCell.SetCellValue((double)detail.FAmount);
                        amountCell.CellStyle = style;

                        amountCount += (double)detail.FAmount;
                        r++;
                    }

                    HSSFRow rowCount = (HSSFRow)sheet.CreateRow(r);
                    for (int rt = 0; rt < 6; rt++)
                    {
                        HSSFCell countCell = (HSSFCell)rowCount.CreateCell(rt);
                        countCell.CellStyle = style;

                        if (rt == 0)
                        {
                            sheet.AddMergedRegion(new Region(r,0,r,2));
                            MoneyUtils money = new MoneyUtils(amountCount);
                            countCell.SetCellValue("合计(大写)：" + money.Convert());
                        }
                        else if (rt == 4)
                        {
                            countCell.SetCellValue("合计");
                        }
                        else if (rt == 5)
                        {
                            countCell.SetCellValue(amountCount);
                        }
                    }
                    r++;

                    HSSFRow rowRemark1 = (HSSFRow)sheet.CreateRow(r);
                    for (int rt = 0; rt < 6; rt++)
                    {
                        HSSFCell remark1Cell = (HSSFCell)rowRemark1.CreateCell(rt);

                        if (rt == 0)
                        {
                            remark1Cell.SetCellValue("签收人：");
                        }
                        else if (rt == 1)
                        {
                            remark1Cell.SetCellValue("制表人：黄叶楷");
                        }
                        else if (rt == 5)
                        {
                            remark1Cell.SetCellValue("单位盖章：");
                        }
                    }
                    r++;

                    HSSFRow rowRemark2 = (HSSFRow)sheet.CreateRow(r);
                    for (int rt = 0; rt < 6; rt++)
                    {
                        HSSFCell remark2Cell = (HSSFCell)rowRemark2.CreateCell(rt);

                        if (rt == 0)
                        {
                            remark2Cell.SetCellValue("①白色：回单");
                        }
                        else if (rt == 1)
                        {
                            remark2Cell.SetCellValue("②红色：客户");
                        }
                        else if (rt == 2)
                        {
                            remark2Cell.SetCellValue("③黄色：仓库");
                        }
                        else if (rt == 5)
                        {
                            remark2Cell.SetCellValue("④蓝色：财务");
                        }
                    }
                }

                workbook.Write(ms);
                response.AddHeader("Content-Disposition", string.Format("attachment; filename=SendOrder_" + sendOrder.FNumber + ".xls"));
                response.BinaryWrite(ms.ToArray());
                workbook = null;
                ms.Close();
                ms.Dispose();
            }
        }

        /// <summary>
        /// 添加送货单(格力)
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void AddSendOrderNewGeLi(InvSendOrderView sendOrderView, string currAccountFId, string currAccountOrgunitFId, out string FId, out string FNumber)
        {

            InvSendOrder sendOrder = new InvSendOrder();
            sendOrder.FId = GuidUtils.getGuid();
            sendOrder.FNumber = ConstData.SEND_ORDER_NUMBER_HEAD + DateUtils.GetCurDateFormat("yyyyMMddHHmmss");
            sendOrder.FName = sendOrder.FNumber;
            sendOrder.FBillDate = DateUtils.ParseStringToDateTime(sendOrderView.FBillDate);
            sendOrder.FCreateTime = DateUtils.GetCurDate();
            sendOrder.FCreatorId = currAccountFId;
            sendOrder.FModifyTime = DateUtils.GetCurDate();
            sendOrder.FModifierId = currAccountFId;
            sendOrder.FRemark = sendOrderView.FRemark;

            sendOrder.FStatusId = sendOrderView.FStatusId;
            //sendOrder.FCustomerId = sendOrderView.FCustomerId;
            sendOrder.FCustomerName = sendOrderView.FCustomerName;
            sendOrder.FCustomerMobile = sendOrderView.FCustomerMobile;
            sendOrder.FCustomerPhone = sendOrderView.FCustomerPhone;
            sendOrder.FCustomerAddress = sendOrderView.FCustomerAddress;
            IBdOrgunitService orgService = new BdOrgunitServiceImpl();
            //String orgunitId=orgService.findOrgunitId(ConstData.ORGUNIT_NAME_LC);
            sendOrder.FOrgunitId = currAccountOrgunitFId;

            if (sendOrderView.FDetails != null)
            {
                foreach (InvSendOrderDetailView detailView in sendOrderView.FDetails)
                {
                    InvSendOrderDetail detail = new InvSendOrderDetail();
                    detail.FId = GuidUtils.getGuid();
                    detail.FProductId = detailView.FProductId;
                    detail.FQty = detailView.FQty;
                    detail.FNumber = detailView.FNumber;
                    detail.FPrice = detailView.FPrice;
                    detail.FAmount = detailView.FAmount;
                    detail.FRemark = detailView.FRemark;

                    sendOrder.InvSendOrderDetail.Add(detail);
                }
            }

            sendOrderDao.addSendOrder(sendOrder);

            FId = sendOrder.FId;
            FNumber = sendOrder.FNumber;
        }

        /// <summary>
        /// 修改送货单(格力)
        /// </summary>
        /// <author>梁建东</author>
        /// <returns></returns>
        public void ModifySendOrderNewGeLi(InvSendOrderView sendOrderView, string currAccountFId)
        {
            InvSendOrder objOld = sendOrderDao.getSendOrderByFId(sendOrderView.FId);

            if (objOld.BdCategory != null && objOld.BdCategory.FNumber.Equals(ConstData.CATEGORY_SEND_ORDER_STATUS_APPROVE))
            {
                throw new Exception(ConstNoteMessage.SEND_ORDER_MODIFY_ERROR);
            }

            if (objOld != null)
            {
                objOld.FNumber = sendOrderView.FNumber;
                objOld.FName = objOld.FNumber;
                objOld.FBillDate = DateUtils.ParseStringToDateTime(sendOrderView.FBillDate);
                objOld.FModifyTime = DateUtils.GetCurDate();
                objOld.FModifierId = currAccountFId;
                objOld.FRemark = sendOrderView.FRemark;
                objOld.FStatusId = sendOrderView.FStatusId;
                //objOld.FCustomerId = sendOrderView.FCustomerId;
                objOld.FCustomerName = sendOrderView.FCustomerName;
                objOld.FCustomerMobile = sendOrderView.FCustomerMobile;
                objOld.FCustomerPhone = sendOrderView.FCustomerPhone;
                objOld.FCustomerAddress = sendOrderView.FCustomerAddress;

                //判断是否删除明细
                List<InvSendOrderDetail> deleteList = new List<InvSendOrderDetail>();
                if (objOld.InvSendOrderDetail != null)
                {
                    foreach (InvSendOrderDetail detail in objOld.InvSendOrderDetail)
                    {
                        if (sendOrderView.FDetails != null)
                        {
                            bool flag = false;
                            foreach (InvSendOrderDetailView detailView in sendOrderView.FDetails)
                            {
                                if (detail.FId.Equals(detailView.FId))
                                {
                                    flag = true;
                                    break;
                                }
                            }
                            if (!flag)
                            {
                                deleteList.Add(detail);
                            }
                        }
                    }
                }

                //添加或修改明细
                if (sendOrderView.FDetails != null)
                {
                    foreach (InvSendOrderDetailView detailView in sendOrderView.FDetails)
                    {
                        if (objOld.InvSendOrderDetail != null && detailView.FId != null && !detailView.FId.Trim().Equals("") && !detailView.FId.Trim().Equals("-1"))
                        {
                            var query = (from d in objOld.InvSendOrderDetail where d.FId == detailView.FId select d).ToList<InvSendOrderDetail>();
                            if (query != null && query.Count > 0)
                            {
                                InvSendOrderDetail detailOld = query.First<InvSendOrderDetail>();
                                detailOld.FProductId = detailView.FProductId;
                                detailOld.FQty = detailView.FQty;
                                detailOld.FNumber = detailView.FNumber;
                                detailOld.FPrice = detailView.FPrice;
                                detailOld.FAmount = detailView.FAmount;
                                detailOld.FRemark = detailView.FRemark;
                            }
                            else
                            {
                                InvSendOrderDetail detail = new InvSendOrderDetail();
                                detail.FId = GuidUtils.getGuid();
                                detail.FProductId = detailView.FProductId;
                                detail.FQty = detailView.FQty;
                                detail.FNumber = detailView.FNumber;
                                detail.FPrice = detailView.FPrice;
                                detail.FAmount = detailView.FAmount;
                                detail.FRemark = detailView.FRemark;

                                objOld.InvSendOrderDetail.Add(detail);
                            }
                        }
                        else
                        {
                            InvSendOrderDetail detail = new InvSendOrderDetail();
                            detail.FId = GuidUtils.getGuid();
                            detail.FProductId = detailView.FProductId;
                            detail.FQty = detailView.FQty;
                            detail.FNumber = detailView.FNumber;
                            detail.FPrice = detailView.FPrice;
                            detail.FAmount = detailView.FAmount;
                            detail.FRemark = detailView.FRemark;

                            objOld.InvSendOrderDetail.Add(detail);
                        }
                    }
                }

                //删除明细
                foreach (InvSendOrderDetail objDelete in deleteList)
                {
                    objOld.InvSendOrderDetail.Remove(objDelete);
                }

                sendOrderDao.Save();
            }
        }
    }
}